#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库管理器
负责用户、样本、日志等数据的存储和管理
"""

import sqlite3
import logging
import os
import json
from pathlib import Path
from datetime import datetime
from typing import Dict, List, Optional, Any
from dataclasses import dataclass
from enum import Enum

# 使用绝对导入，避免相对导入问题
try:
    from src.models.models import User, Sample, OperationLog, UserRole, SampleStatus, SampleInfo, Results, ImageInfo, ModeInfo
except ImportError:
    # 如果绝对导入失败，尝试相对导入
    try:
        from ..models.models import User, Sample, OperationLog, UserRole, SampleStatus, SampleInfo, Results, ImageInfo, ModeInfo
    except ImportError:
        # 如果都失败，使用字符串导入（延迟导入）
        User = Sample = OperationLog = UserRole = SampleStatus = SampleInfo = Results = ImageInfo = ModeInfo = None

logger = logging.getLogger(__name__)

class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self, db_path: str = None):
        if db_path is None:
            # 默认使用项目根目录下的data目录
            project_root = Path(__file__).parent.parent.parent
            data_dir = project_root / "data"
            data_dir.mkdir(exist_ok=True)
            db_path = str(data_dir / "sample_management.db")
        
        self.db_path = db_path
        self.init_database()
    
    def init_database(self):
        """初始化数据库"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 创建用户表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT UNIQUE NOT NULL,
                    password_hash TEXT NOT NULL,
                    role TEXT NOT NULL,
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    last_login TIMESTAMP
                )
            ''')
            
            # 1) 样本主表 - 存放header类信息
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS lis_samples (
                    id TEXT PRIMARY KEY,
                    
                    -- 设备/协议信息
                    protocol TEXT NOT NULL DEFAULT 'CUSTOM_LIS',
                    machine_model TEXT NOT NULL,
                    machine_id TEXT NOT NULL,
                    
                    -- 样本信息
                    sample_no INTEGER NOT NULL,
                    sample_uid TEXT NOT NULL,
                    position_no TEXT NOT NULL,
                    sample_type TEXT NOT NULL,
                    analysis_datetime DATETIME NOT NULL,
                    receive_time DATETIME(3) NOT NULL,
                    
                    -- 患者信息
                    patient_name TEXT,
                    patient_age INTEGER,
                    patient_gender TEXT,
                    
                    -- 分析模式信息
                    analysis_mode TEXT NOT NULL,
                    mode_name_zh TEXT,
                    mode_desc_zh TEXT,
                    mode_typical_fields_json TEXT,  -- JSON数组格式
                    
                    -- 图片元数据
                    image_filename TEXT,
                    image_path TEXT,
                    image_size_bytes INTEGER,
                    image_data_length_hex INTEGER,
                    image_format TEXT,
                    image_saved_flag BOOLEAN,
                    
                    -- 原始报文
                    raw_payload TEXT NOT NULL,
                    
                    -- 维护字段
                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    
                    -- 状态字段
                    status TEXT DEFAULT 'PENDING'
                )
            ''')
            
            # 2) 通用检验结果表（Key-Value结构）
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS lis_sample_results (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    sample_id TEXT NOT NULL,
                    analyte_code TEXT NOT NULL,
                    value_num DECIMAL(10,4) NOT NULL,
                    unit TEXT NOT NULL,
                    raw_value TEXT NOT NULL,
                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (sample_id) REFERENCES lis_samples(id) ON DELETE CASCADE
                )
            ''')
            
            # 3) 峰图数据表（TIME/AREA）
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS lis_sample_peaks (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    sample_id TEXT NOT NULL,
                    peak_index INTEGER NOT NULL,
                    time_sec DECIMAL(10,2),
                    area DECIMAL(18,4),
                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (sample_id) REFERENCES lis_samples(id) ON DELETE CASCADE
                )
            ''')
            
            # 创建索引
            cursor.execute('''
                CREATE INDEX IF NOT EXISTS idx_lis_samples_machine_sample 
                ON lis_samples(machine_id, sample_uid)
            ''')
            
            cursor.execute('''
                CREATE INDEX IF NOT EXISTS idx_lis_samples_time 
                ON lis_samples(analysis_datetime)
            ''')
            
            cursor.execute('''
                CREATE INDEX IF NOT EXISTS idx_lis_samples_machine_time 
                ON lis_samples(machine_id, analysis_datetime)
            ''')
            
            cursor.execute('''
                CREATE INDEX IF NOT EXISTS idx_lis_sample_results_analyte 
                ON lis_sample_results(analyte_code)
            ''')
            
            cursor.execute('''
                CREATE INDEX IF NOT EXISTS idx_lis_sample_peaks_sample 
                ON lis_sample_peaks(sample_id)
            ''')
            
            # 创建操作日志表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS operation_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    operator TEXT NOT NULL,
                    operation_type TEXT NOT NULL,
                    operation_content TEXT,
                    ip_address TEXT
                )
            ''')
            
            # 创建系统配置表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS system_config (
                    key TEXT PRIMARY KEY,
                    value TEXT,
                    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建检测结果警告区间表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS test_warning_ranges (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    test_name TEXT NOT NULL,
                    min_value REAL,
                    max_value REAL,
                    unit TEXT,
                    warning_color TEXT DEFAULT 'red',
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建备份配置表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS backup_config (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    auto_backup BOOLEAN DEFAULT 1,
                    auto_backup_interval INTEGER DEFAULT 24,
                    close_backup BOOLEAN DEFAULT 1,
                    encryption_type TEXT DEFAULT 'AES',
                    max_backup_files INTEGER DEFAULT 30,
                    backup_database BOOLEAN DEFAULT 1,
                    backup_config BOOLEAN DEFAULT 1,
                    backup_logs BOOLEAN DEFAULT 1,
                    backup_path TEXT,
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建备份日志表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS backup_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    backup_name TEXT NOT NULL,
                    backup_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    backup_path TEXT,
                    file_size INTEGER,
                    encryption_type TEXT,
                    backup_type TEXT,
                    status TEXT,
                    message TEXT,
                    operator TEXT,
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建图片数据表（用于存储大型图片数据）
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS sample_images (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    sample_id TEXT NOT NULL,
                    filename TEXT,
                    hex_data TEXT,     -- 十六进制数据
                    base64_data TEXT,  -- Base64数据
                    file_size INTEGER,
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (sample_id) REFERENCES lis_samples (id) ON DELETE CASCADE
                )
            ''')
            
            # 创建加密密钥表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS encryption_keys (
                    key_name TEXT PRIMARY KEY,
                    public_key TEXT,
                    private_key TEXT,
                    key_type TEXT,
                    key_size INTEGER,
                    description TEXT,
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            conn.commit()
            logger.info("数据库初始化完成")
    
    def create_admin_user(self, username: str, password: str) -> bool:
        """创建管理员用户"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "INSERT INTO users (username, password_hash, role) VALUES (?, ?, ?)",
                    (username, self._hash_password(password), UserRole.ADMIN.value)
                )
                conn.commit()
                return True
        except sqlite3.IntegrityError:
            return False
    
    def verify_user(self, username: str, password: str) -> Optional[User]:
        """验证用户登录"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute(
                "SELECT * FROM users WHERE username = ? AND password_hash = ?",
                (username, self._hash_password(password))
            )
            result = cursor.fetchone()
            
            if result:
                # 更新最后登录时间
                cursor.execute(
                    "UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = ?",
                    (result[0],)
                )
                conn.commit()
                
                return User(
                    id=result[0],
                    username=result[1],
                    password_hash=result[2],
                    role=UserRole(result[3]),
                    created_time=datetime.fromisoformat(result[4]),
                    last_login=datetime.fromisoformat(result[5]) if result[5] else None
                )
            return None
    
    def _hash_password(self, password: str) -> str:
        """简单的密码哈希（实际应用中应使用更安全的方法）"""
        import hashlib
        return hashlib.sha256(password.encode()).hexdigest()
    
    def _parse_sample_status(self, status_str: str) -> SampleStatus:
        """解析样本状态字符串为枚举值"""
        if not status_str:
            return SampleStatus.PENDING
        
        # 映射数据库中的状态字符串到枚举值
        status_mapping = {
            'PENDING': SampleStatus.PENDING,
            '待处理': SampleStatus.PENDING,
            'PROCESSED': SampleStatus.PROCESSED,
            '已处理': SampleStatus.PROCESSED,
            'EXPORTED': SampleStatus.EXPORTED,
            '已导出': SampleStatus.EXPORTED,
            'SENT': SampleStatus.SENT,
            '已发送': SampleStatus.SENT
        }
        
        return status_mapping.get(status_str, SampleStatus.PENDING)
    
    def get_samples(self) -> List[Sample]:
        """获取所有样本 - 支持新的三表结构"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 获取主表数据
                cursor.execute('''
                    SELECT s.*, si.hex_data, si.base64_data
                    FROM lis_samples s
                    LEFT JOIN sample_images si ON s.id = si.sample_id
                    ORDER BY s.receive_time DESC
                ''')
                
                rows = cursor.fetchall()
                samples = []
                
                for row in rows:
                    try:
                        # 解析主表数据
                        sample_id = row[0]
                        protocol = row[1]
                        machine_model = row[2]
                        machine_id = row[3]
                        sample_no = row[4]
                        sample_uid = row[5]
                        position_no = row[6]
                        sample_type = row[7]
                        analysis_datetime = row[8]
                        receive_time = row[9]
                        patient_name = row[10]
                        patient_age = row[11]
                        patient_gender = row[12]
                        analysis_mode = row[13]
                        mode_name_zh = row[14]
                        mode_desc_zh = row[15]
                        mode_typical_fields_json = row[16]
                        image_filename = row[17]
                        image_path = row[18]
                        image_size_bytes = row[19]
                        image_data_length_hex = row[20]
                        image_format = row[21]
                        image_saved_flag = row[22]
                        raw_payload = row[23]
                        created_at = row[24]
                        updated_at = row[25]
                        status = row[26]
                        hex_data = row[27]
                        base64_data = row[28]
                        
                        # 获取检测结果数据
                        cursor.execute('''
                            SELECT analyte_code, value_num, unit, raw_value
                            FROM lis_sample_results
                            WHERE sample_id = ?
                            ORDER BY analyte_code
                        ''', (sample_id,))
                        
                        results_data = {}
                        for result_row in cursor.fetchall():
                            analyte_code = result_row[0]
                            value_num = result_row[1]
                            unit = result_row[2]
                            raw_value = result_row[3]
                            results_data[analyte_code] = raw_value
                        
                        # 获取峰图数据
                        cursor.execute('''
                            SELECT peak_index, time_sec, area
                            FROM lis_sample_peaks
                            WHERE sample_id = ?
                            ORDER BY peak_index
                        ''', (sample_id,))
                        
                        time_data = []
                        area_data = []
                        for peak_row in cursor.fetchall():
                            peak_index = peak_row[0]
                            time_sec = peak_row[1]
                            area = peak_row[2]
                            
                            # 填充TIME和AREA数组
                            while len(time_data) < peak_index:
                                time_data.append("00.00")
                            while len(area_data) < peak_index:
                                area_data.append("00.00")
                            
                            if time_sec is not None:
                                time_data[peak_index - 1] = f"{time_sec:.2f}"
                            if area is not None:
                                area_data[peak_index - 1] = f"{area:.4f}"
                        
                        # 创建SampleInfo对象
                        sample_info = SampleInfo(
                            sample_id=sample_uid,
                            sample_number=str(sample_no),
                            patient_name=patient_name or "",
                            patient_age=patient_age or 0,
                            patient_gender=patient_gender or "",
                            datetime=analysis_datetime,
                            sample_type=sample_type,
                            sample_type_name="",
                            sample_category="",
                            report_id="",
                            position=position_no
                        )
                        
                        # 创建Results对象
                        results = Results(
                            HbA1a=results_data.get('HbA1a', ''),
                            HbA1b=results_data.get('HbA1b', ''),
                            HbF=results_data.get('HbF', ''),
                            LA1c=results_data.get('LA1c', ''),
                            HbA1c=results_data.get('HbA1c', ''),
                            HbA0=results_data.get('HbA0', ''),
                            eAG=results_data.get('eAG', ''),
                            A2=results_data.get('A2', ''),
                            HbE=results_data.get('HbE', ''),
                            HbD=results_data.get('HbD', ''),
                            HbS=results_data.get('HbS', ''),
                            HbC=results_data.get('HbC', ''),
                            p3=results_data.get('p3', ''),
                            v_win=results_data.get('v_win', ''),
                            TIME=time_data,
                            AREA=area_data,
                            TIME_DETAILS={},
                            AREA_DETAILS={},
                            other_results=results_data
                        )
                        
                        # 创建ImageInfo对象
                        image = ImageInfo(
                            filename=image_filename or "",
                            size=image_size_bytes or 0,
                            path=image_path or "",
                            saved=bool(image_saved_flag),
                            hex_data=hex_data or "",
                            base64_data=base64_data or "",
                            error=""
                        )
                        
                        # 创建ModeInfo对象
                        typical_fields = []
                        if mode_typical_fields_json:
                            try:
                                typical_fields = json.loads(mode_typical_fields_json)
                            except:
                                typical_fields = []
                        
                        mode_info = ModeInfo(
                            name=mode_name_zh or "",
                            description=mode_desc_zh or "",
                            mode_name=mode_name_zh or "",
                            mode_description=mode_desc_zh or "",
                            test_count=len(typical_fields),
                            test_items=typical_fields,
                            typical_fields=typical_fields
                        )
                        
                        # 创建Sample对象
                        sample = Sample(
                            id=sample_id,
                            protocol=protocol,
                            machine_model=machine_model,
                            machine_id=machine_id,
                            sample_info=sample_info,
                            results=results,
                            image=image,
                            analysis_mode=analysis_mode,
                            mode_info=mode_info,
                            raw=raw_payload,
                            receive_time=datetime.fromisoformat(receive_time) if receive_time else datetime.now(),
                            status=self._parse_sample_status(status),
                            created_time=datetime.fromisoformat(created_at) if created_at else datetime.now(),
                            updated_time=datetime.fromisoformat(updated_at) if updated_at else datetime.now()
                        )
                        samples.append(sample)
                        
                    except Exception as e:
                        logger.error(f"解析样本数据失败: {e}, row: {row}")
                        continue
                
                return samples
                
        except Exception as e:
            logger.error(f"获取样本列表失败: {e}")
            return []

    def add_sample(self, sample: Sample) -> bool:
        """添加样本 - 兼容旧版本接口"""
        try:
            # 将Sample对象转换为JSON格式
            json_data = {
                "protocol": sample.protocol,
                "machine_model": sample.machine_model,
                "machine_id": sample.machine_id,
                "sample_info": {
                    "datetime": sample.sample_info.datetime,
                    "sample_no": sample.sample_info.sample_number,
                    "sample_id": sample.sample_info.sample_id,
                    "position": sample.sample_info.position,
                    "sample_type": sample.sample_info.sample_type,
                    "patient_name": sample.sample_info.patient_name,
                    "patient_age": sample.sample_info.patient_age,
                    "patient_gender": sample.sample_info.patient_gender
                },
                "results": {
                    "HbA1a": sample.results.HbA1a,
                    "HbA1b": sample.results.HbA1b,
                    "HbF": sample.results.HbF,
                    "LA1c": sample.results.LA1c,
                    "HbA1c": sample.results.HbA1c,
                    "HbA0": sample.results.HbA0,
                    "eAG": sample.results.eAG,
                    "A2": sample.results.A2,
                    "HbE": sample.results.HbE,
                    "HbD": sample.results.HbD,
                    "HbS": sample.results.HbS,
                    "HbC": sample.results.HbC,
                    "p3": sample.results.p3,
                    "v_win": sample.results.v_win,
                    "TIME": sample.results.TIME,
                    "AREA": sample.results.AREA
                },
                "image": {
                    "filename": sample.image.filename,
                    "size": sample.image.size,
                    "path": sample.image.path,
                    "saved": sample.image.saved
                },
                "analysis_mode": sample.analysis_mode.value if hasattr(sample.analysis_mode, 'value') else str(sample.analysis_mode),
                "mode_info": {
                    "name": sample.mode_info.name,
                    "description": sample.mode_info.description,
                    "typical_fields": sample.mode_info.typical_fields
                },
                "raw": sample.raw,
                "receive_time": sample.receive_time.isoformat()
            }
            
            # 调用新的入库方法
            return self.add_sample_from_serial_data(json_data)
            
        except Exception as e:
            logger.error(f"添加样本失败: {e}")
            return False
    
    def update_sample(self, sample: Sample) -> bool:
        """更新样本 - 兼容旧版本接口"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 首先删除旧的检测结果和峰图数据
                cursor.execute('DELETE FROM lis_sample_results WHERE sample_id = ?', (sample.id,))
                cursor.execute('DELETE FROM lis_sample_peaks WHERE sample_id = ?', (sample.id,))
                
                # 更新主表数据
                cursor.execute('''
                    UPDATE lis_samples SET
                        protocol = ?, machine_model = ?, machine_id = ?,
                        sample_no = ?, sample_uid = ?, position_no = ?, sample_type = ?, analysis_datetime = ?,
                        patient_name = ?, patient_age = ?, patient_gender = ?,
                        analysis_mode = ?, mode_name_zh = ?, mode_desc_zh = ?, mode_typical_fields_json = ?,
                        image_filename = ?, image_path = ?, image_size_bytes = ?, image_data_length_hex = ?, 
                        image_format = ?, image_saved_flag = ?, raw_payload = ?, updated_at = CURRENT_TIMESTAMP
                    WHERE id = ?
                ''', (
                    sample.protocol, sample.machine_model, sample.machine_id,
                    int(sample.sample_info.sample_number) if sample.sample_info.sample_number.isdigit() else 0,
                    sample.sample_info.sample_id, sample.sample_info.position, sample.sample_info.sample_type,
                    sample.sample_info.datetime, 
                    sample.sample_info.patient_name, sample.sample_info.patient_age, sample.sample_info.patient_gender,
                    sample.analysis_mode.value if hasattr(sample.analysis_mode, 'value') else str(sample.analysis_mode), 
                    sample.mode_info.name,
                    sample.mode_info.description, json.dumps(sample.mode_info.typical_fields, ensure_ascii=False),
                    sample.image.filename, sample.image.path, sample.image.size, 0, '', sample.image.saved,
                    sample.raw, sample.id
                ))
                
                # 重新插入检测结果数据
                analyte_units = {
                    'HbA1a': '%', 'HbA1b': '%', 'HbF': '%', 'LA1c': '%', 'HbA1c': '%', 
                    'HbA0': '%', 'p3': '%', 'A2': '%', 'HbE': '%', 'HbD': '%', 
                    'HbS': '%', 'HbC': '%', 'eAG': 'mg/dL'
                }
                
                results_data = {
                    'HbA1a': sample.results.HbA1a,
                    'HbA1b': sample.results.HbA1b,
                    'HbF': sample.results.HbF,
                    'LA1c': sample.results.LA1c,
                    'HbA1c': sample.results.HbA1c,
                    'HbA0': sample.results.HbA0,
                    'eAG': sample.results.eAG,
                    'A2': sample.results.A2,
                    'HbE': sample.results.HbE,
                    'HbD': sample.results.HbD,
                    'HbS': sample.results.HbS,
                    'HbC': sample.results.HbC,
                    'p3': sample.results.p3,
                    'v_win': sample.results.v_win
                }
                
                for analyte_code, raw_value in results_data.items():
                    # 保存所有数据，包括"00.00"和空值
                    if raw_value is not None:  # 只检查是否为None，不排除"00.00"
                        try:
                            # 尝试转换为数值，如果失败则使用0.0
                            if raw_value and raw_value != "":
                                value_num = float(raw_value)
                            else:
                                value_num = 0.0
                            
                            unit = analyte_units.get(analyte_code, '%')
                            
                            cursor.execute('''
                                INSERT INTO lis_sample_results (sample_id, analyte_code, value_num, unit, raw_value)
                                VALUES (?, ?, ?, ?, ?)
                            ''', (sample.id, analyte_code, value_num, unit, raw_value))
                        except (ValueError, TypeError):
                            # 如果转换失败，仍然保存原始值
                            logger.warning(f"无法解析检测结果: {analyte_code}={raw_value}，保存原始值")
                            cursor.execute('''
                                INSERT INTO lis_sample_results (sample_id, analyte_code, value_num, unit, raw_value)
                                VALUES (?, ?, ?, ?, ?)
                            ''', (sample.id, analyte_code, 0.0, analyte_units.get(analyte_code, '%'), raw_value))
                
                # 重新插入峰图数据
                time_array = sample.results.TIME
                area_array = sample.results.AREA
                
                max_length = max(len(time_array), len(area_array))
                
                for i in range(max_length):
                    peak_index = i + 1
                    time_sec = None
                    area = None
                    
                    # 保存TIME数据，包括"00.00"
                    if i < len(time_array) and time_array[i] is not None:
                        try:
                            if time_array[i] and time_array[i] != "":
                                time_sec = float(time_array[i])
                            else:
                                time_sec = 0.0
                        except (ValueError, TypeError):
                            # 如果转换失败，保存为0.0
                            time_sec = 0.0
                    
                    # 保存AREA数据，包括"00.00"
                    if i < len(area_array) and area_array[i] is not None:
                        try:
                            if area_array[i] and area_array[i] != "":
                                area = float(area_array[i])
                            else:
                                area = 0.0
                        except (ValueError, TypeError):
                            # 如果转换失败，保存为0.0
                            area = 0.0
                    
                    cursor.execute('''
                        INSERT INTO lis_sample_peaks (sample_id, peak_index, time_sec, area)
                        VALUES (?, ?, ?, ?)
                    ''', (sample.id, peak_index, time_sec, area))
                
                conn.commit()
                logger.info(f"成功更新样本: {sample.id}")
                return True
                
        except Exception as e:
            logger.error(f"更新样本失败: {e}")
            return False
    
    def add_sample_from_serial_data(self, json_data: Dict[str, Any]) -> bool:
        """从串口通信数据添加样本 - 支持新的三表结构"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                # 解析JSON数据
                protocol = json_data.get('protocol', 'CUSTOM_LIS')
                machine_model = json_data.get('machine_model', '')
                machine_id = json_data.get('machine_id', '')
                
                # 解析样本信息
                sample_info = json_data.get('sample_info', {})
                sample_no = int(sample_info.get('sample_no', 0))
                sample_uid = sample_info.get('sample_id', '')
                position_no = sample_info.get('position', '')
                sample_type = sample_info.get('sample_type', '')
                analysis_datetime = sample_info.get('datetime', '')
                
                # 解析患者信息
                patient_name = sample_info.get('patient_name', '')
                patient_age = sample_info.get('patient_age', None)
                patient_gender = sample_info.get('patient_gender', '')
                
                # 解析分析模式信息
                analysis_mode = json_data.get('analysis_mode', '')
                mode_info = json_data.get('mode_info', {})
                mode_name_zh = mode_info.get('name', '')
                mode_desc_zh = mode_info.get('description', '')
                typical_fields = mode_info.get('typical_fields', [])
                mode_typical_fields_json = json.dumps(typical_fields, ensure_ascii=False)
                
                # 解析图片信息
                image_info = json_data.get('image', {})
                image_filename = image_info.get('filename', '')
                image_path = image_info.get('path', '')
                image_size_bytes = image_info.get('size', 0)
                image_data_length_hex = image_info.get('data_length', 0)
                image_format = image_info.get('format', '')
                image_saved_flag = image_info.get('saved', False)
                
                # 原始报文和接收时间
                raw_payload = json_data.get('raw', '')
                receive_time = json_data.get('receive_time', datetime.now().isoformat())
                
                # 生成样本ID
                sample_id = f"SAMPLE_{int(datetime.now().timestamp() * 1000)}_{hash(sample_uid) % 10000:04x}"
                
                # 1. 插入主表数据
                cursor.execute('''
                    INSERT INTO lis_samples (
                        id, protocol, machine_model, machine_id,
                        sample_no, sample_uid, position_no, sample_type, analysis_datetime, receive_time,
                        patient_name, patient_age, patient_gender,
                        analysis_mode, mode_name_zh, mode_desc_zh, mode_typical_fields_json,
                        image_filename, image_path, image_size_bytes, image_data_length_hex, image_format, image_saved_flag,
                        raw_payload, created_at, updated_at, status
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    sample_id, protocol, machine_model, machine_id,
                    sample_no, sample_uid, position_no, sample_type, analysis_datetime, receive_time,
                    patient_name, patient_age, patient_gender,
                    analysis_mode, mode_name_zh, mode_desc_zh, mode_typical_fields_json,
                    image_filename, image_path, image_size_bytes, image_data_length_hex, image_format, image_saved_flag,
                    raw_payload, datetime.now().isoformat(), datetime.now().isoformat(), 'PENDING'
                ))
                
                # 2. 插入检测结果数据
                results = json_data.get('results', {})
                
                # 定义检测项目及其单位
                analyte_units = {
                    'HbA1a': '%', 'HbA1b': '%', 'HbF': '%', 'LA1c': '%', 'HbA1c': '%', 
                    'HbA0': '%', 'p3': '%', 'A2': '%', 'HbE': '%', 'HbD': '%', 
                    'HbS': '%', 'HbC': '%', 'eAG': 'mg/dL'  # eAG单位需要根据实际情况调整
                }
                
                for analyte_code, raw_value in results.items():
                    # 跳过数组字段
                    if analyte_code in ['TIME', 'AREA', 'TIME_DETAILS', 'AREA_DETAILS']:
                        continue
                    
                    if raw_value and raw_value != "00.00":
                        try:
                            # 转换数值
                            value_num = float(raw_value)
                            unit = analyte_units.get(analyte_code, '%')
                    
                            cursor.execute('''
                                INSERT INTO lis_sample_results (sample_id, analyte_code, value_num, unit, raw_value)
                                VALUES (?, ?, ?, ?, ?)
                            ''', (sample_id, analyte_code, value_num, unit, raw_value))
                        except (ValueError, TypeError):
                            logger.warning(f"无法解析检测结果: {analyte_code}={raw_value}")
                            continue
                
                # 3. 插入峰图数据
                time_array = results.get('TIME', [])
                area_array = results.get('AREA', [])
                
                # 取最大长度
                max_length = max(len(time_array), len(area_array))
                
                for i in range(max_length):
                    peak_index = i + 1
                    time_sec = None
                    area = None
                    
                    if i < len(time_array) and time_array[i] and time_array[i] != "00.00":
                        try:
                            time_sec = float(time_array[i])
                        except (ValueError, TypeError):
                            pass
                    
                    if i < len(area_array) and area_array[i] and area_array[i] != "00.00":
                        try:
                            area = float(area_array[i])
                        except (ValueError, TypeError):
                            pass
                    
                    cursor.execute('''
                        INSERT INTO lis_sample_peaks (sample_id, peak_index, time_sec, area)
                        VALUES (?, ?, ?, ?)
                    ''', (sample_id, peak_index, time_sec, area))
                
                # 4. 如果有图片数据，插入图片表
                if image_filename and (image_info.get('hex_data') or image_info.get('base64_data')):
                    cursor.execute('''
                        INSERT INTO sample_images (sample_id, filename, hex_data, base64_data, file_size)
                        VALUES (?, ?, ?, ?, ?)
                ''', (
                        sample_id, 
                        image_filename,
                        image_info.get('hex_data', ''),
                        image_info.get('base64_data', ''),
                        image_size_bytes
                    ))
                
                conn.commit()
                logger.info(f"成功添加样本: {sample_id}")
                return True
                
        except Exception as e:
            logger.error(f"添加样本失败: {e}")
            return False
    
    def search_samples(self, search_term: str) -> List[Sample]:
        """搜索样本"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT s.*, si.hex_data, si.base64_data
                    FROM lis_samples s
                    LEFT JOIN sample_images si ON s.id = si.sample_id
                    WHERE s.sample_uid LIKE ? OR s.machine_id LIKE ? OR s.sample_no LIKE ? OR s.patient_name LIKE ?
                    ORDER BY s.receive_time DESC
                ''', (f'%{search_term}%', f'%{search_term}%', f'%{search_term}%', f'%{search_term}%'))
                
                rows = cursor.fetchall()
                samples = []
                
                for row in rows:
                    try:
                        # 解析主表数据
                        sample_id = row[0]
                        protocol = row[1]
                        machine_model = row[2]
                        machine_id = row[3]
                        sample_no = row[4]
                        sample_uid = row[5]
                        position_no = row[6]
                        sample_type = row[7]
                        analysis_datetime = row[8]
                        receive_time = row[9]
                        patient_name = row[10]
                        patient_age = row[11]
                        patient_gender = row[12]
                        analysis_mode = row[13]
                        mode_name_zh = row[14]
                        mode_desc_zh = row[15]
                        mode_typical_fields_json = row[16]
                        image_filename = row[17]
                        image_path = row[18]
                        image_size_bytes = row[19]
                        image_data_length_hex = row[20]
                        image_format = row[21]
                        image_saved_flag = row[22]
                        raw_payload = row[23]
                        created_at = row[24]
                        updated_at = row[25]
                        status = row[26]
                        hex_data = row[27]
                        base64_data = row[28]
                        
                        # 获取检测结果数据
                        cursor.execute('''
                            SELECT analyte_code, value_num, unit, raw_value
                            FROM lis_sample_results
                            WHERE sample_id = ?
                            ORDER BY analyte_code
                        ''', (sample_id,))
                        
                        results_data = {}
                        for result_row in cursor.fetchall():
                            analyte_code = result_row[0]
                            value_num = result_row[1]
                            unit = result_row[2]
                            raw_value = result_row[3]
                            results_data[analyte_code] = raw_value
                        
                        # 获取峰图数据
                        cursor.execute('''
                            SELECT peak_index, time_sec, area
                            FROM lis_sample_peaks
                            WHERE sample_id = ?
                            ORDER BY peak_index
                        ''', (sample_id,))
                        
                        time_data = []
                        area_data = []
                        for peak_row in cursor.fetchall():
                            peak_index = peak_row[0]
                            time_sec = peak_row[1]
                            area = peak_row[2]
                            
                            # 填充TIME和AREA数组
                            while len(time_data) < peak_index:
                                time_data.append("00.00")
                            while len(area_data) < peak_index:
                                area_data.append("00.00")
                            
                            if time_sec is not None:
                                time_data[peak_index - 1] = f"{time_sec:.2f}"
                            if area is not None:
                                area_data[peak_index - 1] = f"{area:.4f}"
                        
                        # 创建SampleInfo对象
                        sample_info = SampleInfo(
                            sample_id=sample_uid,
                            sample_number=str(sample_no),
                            patient_name=patient_name or "",
                            patient_age=patient_age or 0,
                            patient_gender=patient_gender or "",
                            datetime=analysis_datetime,
                            sample_type=sample_type,
                            sample_type_name="",
                            sample_category="",
                            report_id="",
                            position=position_no
                        )
                        
                        # 创建Results对象
                        results = Results(
                            HbA1a=results_data.get('HbA1a', ''),
                            HbA1b=results_data.get('HbA1b', ''),
                            HbF=results_data.get('HbF', ''),
                            LA1c=results_data.get('LA1c', ''),
                            HbA1c=results_data.get('HbA1c', ''),
                            HbA0=results_data.get('HbA0', ''),
                            eAG=results_data.get('eAG', ''),
                            A2=results_data.get('A2', ''),
                            HbE=results_data.get('HbE', ''),
                            HbD=results_data.get('HbD', ''),
                            HbS=results_data.get('HbS', ''),
                            HbC=results_data.get('HbC', ''),
                            p3=results_data.get('p3', ''),
                            v_win=results_data.get('v_win', ''),
                            TIME=time_data,
                            AREA=area_data,
                            TIME_DETAILS={},
                            AREA_DETAILS={},
                            other_results=results_data
                        )
                        
                        # 创建ImageInfo对象
                        image = ImageInfo(
                            filename=image_filename or "",
                            size=image_size_bytes or 0,
                            path=image_path or "",
                            saved=bool(image_saved_flag),
                            hex_data=hex_data or "",
                            base64_data=base64_data or "",
                            error=""
                        )
                        
                        # 创建ModeInfo对象
                        typical_fields = []
                        if mode_typical_fields_json:
                            try:
                                typical_fields = json.loads(mode_typical_fields_json)
                            except:
                                typical_fields = []
                        
                        mode_info = ModeInfo(
                            name=mode_name_zh or "",
                            description=mode_desc_zh or "",
                            mode_name=mode_name_zh or "",
                            mode_description=mode_desc_zh or "",
                            test_count=len(typical_fields),
                            test_items=typical_fields,
                            typical_fields=typical_fields
                        )
                        
                        # 创建Sample对象
                        sample = Sample(
                            id=sample_id,
                            protocol=protocol,
                            machine_model=machine_model,
                            machine_id=machine_id,
                            sample_info=sample_info,
                            results=results,
                            image=image,
                            analysis_mode=analysis_mode,
                            mode_info=mode_info,
                            raw=raw_payload,
                            receive_time=datetime.fromisoformat(receive_time) if receive_time else datetime.now(),
                            status=self._parse_sample_status(status),
                            created_time=datetime.fromisoformat(created_at) if created_at else datetime.now(),
                            updated_time=datetime.fromisoformat(updated_at) if updated_at else datetime.now()
                        )
                        samples.append(sample)
                        
                    except Exception as e:
                        logger.error(f"解析样本数据失败: {e}, row: {row}")
                        continue
                
                return samples
                
        except Exception as e:
            logger.error(f"搜索样本失败: {e}")
            return []
    
    def add_operation_log(self, log: OperationLog) -> bool:
        """添加操作日志"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO operation_logs (operator, operation_type, operation_content, ip_address)
                    VALUES (?, ?, ?, ?)
                ''', (log.operator, log.operation_type, log.operation_content, log.ip_address))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"添加操作日志失败: {e}")
            return False
    
    def get_operation_logs(self, filters: Dict = None) -> List[OperationLog]:
        """获取操作日志"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            query = "SELECT * FROM operation_logs"
            params = []
            
            if filters:
                conditions = []
                for key, value in filters.items():
                    if value:
                        if key == "date_range":
                            conditions.append("operation_time BETWEEN ? AND ?")
                            params.extend(value)
                        else:
                            conditions.append(f"{key} LIKE ?")
                            params.append(f"%{value}%")
                
                if conditions:
                    query += " WHERE " + " AND ".join(conditions)
            
            query += " ORDER BY operation_time DESC"
            
            cursor.execute(query, params)
            rows = cursor.fetchall()
            
            logs = []
            for row in rows:
                log = OperationLog(
                    id=row[0],
                    operation_time=datetime.fromisoformat(row[1]),
                    operator=row[2],
                    operation_type=row[3],
                    operation_content=row[4],
                    ip_address=row[5] if row[5] else ""
                )
                logs.append(log)
            
            return logs
    
    def get_system_config(self, key: str) -> Optional[str]:
        """获取系统配置"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT value FROM system_config WHERE key = ?", (key,))
                result = cursor.fetchone()
                return result[0] if result else None
        except Exception as e:
            logger.error(f"获取系统配置失败: {e}")
            return None
    
    def set_system_config(self, key: str, value: str) -> bool:
        """设置系统配置"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT OR REPLACE INTO system_config (key, value, updated_time)
                    VALUES (?, ?, CURRENT_TIMESTAMP)
                ''', (key, value))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"设置系统配置失败: {e}")
            return False
    
    def get_warning_ranges(self, filters: Dict = None) -> List:
        """获取警告区间列表"""
        from src.models.models import WarningRange
        
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                
                query = "SELECT * FROM test_warning_ranges"
                params = []
                
                if filters:
                    conditions = []
                    for key, value in filters.items():
                        if value:
                            conditions.append(f"{key} LIKE ?")
                            params.append(f"%{value}%")
                    
                    if conditions:
                        query += " WHERE " + " AND ".join(conditions)
                
                query += " ORDER BY created_time DESC"
                
                cursor.execute(query, params)
                rows = cursor.fetchall()
                
                warning_ranges = []
                for row in rows:
                    warning_range = WarningRange(
                        test_name=row[1],
                        min_value=row[2],
                        max_value=row[3],
                        unit=row[4],
                        warning_color=row[5],
                        created_time=datetime.fromisoformat(row[6]) if row[6] else datetime.now()
                    )
                    warning_ranges.append(warning_range)
                
                return warning_ranges
                
        except Exception as e:
            logger.error(f"获取警告区间失败: {e}")
            return []
    
    def add_warning_range(self, warning_range) -> bool:
        """添加警告区间"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO test_warning_ranges (test_name, min_value, max_value, unit, warning_color)
                    VALUES (?, ?, ?, ?, ?)
                ''', (
                    warning_range.test_name,
                    warning_range.min_value,
                    warning_range.max_value,
                    warning_range.unit,
                    warning_range.warning_color
                ))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"添加警告区间失败: {e}")
            return False
    
    def update_warning_range(self, warning_range) -> bool:
        """更新警告区间"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    UPDATE test_warning_ranges 
                    SET min_value = ?, max_value = ?, unit = ?, warning_color = ?
                    WHERE test_name = ?
                ''', (
                    warning_range.min_value,
                    warning_range.max_value,
                    warning_range.unit,
                    warning_range.warning_color,
                    warning_range.test_name
                ))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"更新警告区间失败: {e}")
            return False
    
    def delete_warning_range(self, test_name: str) -> bool:
        """删除警告区间"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('DELETE FROM test_warning_ranges WHERE test_name = ?', (test_name,))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"删除警告区间失败: {e}")
            return False
    
    def get_encryption_key(self, key_name: str) -> Optional[Dict]:
        """获取加密密钥"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT key_name, public_key, private_key, key_type, key_size, description, created_time
                    FROM encryption_keys 
                    WHERE key_name = ?
                ''', (key_name,))
                result = cursor.fetchone()
                
                if result:
                    return {
                        "key_name": result[0],
                        "public_key": result[1],
                        "private_key": result[2],
                        "key_type": result[3],
                        "key_size": result[4],
                        "description": result[5],
                        "created_time": result[6]
                    }
                return None
        except Exception as e:
            logger.error(f"获取加密密钥失败: {e}")
            return None
    
    def store_encryption_key(self, key_name: str, public_key: str, private_key: str, 
                           key_type: str, key_size: int, description: str = "") -> bool:
        """存储加密密钥"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT OR REPLACE INTO encryption_keys 
                    (key_name, public_key, private_key, key_type, key_size, description, created_time)
                    VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
                ''', (key_name, public_key, private_key, key_type, key_size, description))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"存储加密密钥失败: {e}")
            return False
    
    def delete_encryption_key(self, key_name: str) -> bool:
        """删除加密密钥"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('DELETE FROM encryption_keys WHERE key_name = ?', (key_name,))
                conn.commit()
                return True
        except Exception as e:
            logger.error(f"删除加密密钥失败: {e}")
            return False
    
    def list_encryption_keys(self) -> List[Dict]:
        """列出所有加密密钥"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT key_name, key_type, key_size, description, created_time
                    FROM encryption_keys
                    ORDER BY created_time DESC
                ''')
                results = cursor.fetchall()
                
                keys = []
                for result in results:
                    keys.append({
                        "key_name": result[0],
                        "key_type": result[1],
                        "key_size": result[2],
                        "description": result[3],
                        "created_time": result[4]
                    })
                return keys
        except Exception as e:
            logger.error(f"列出加密密钥失败: {e}")
            return [] 